import os
import pandas as pd
import xlwings as xw
from openpyxl import load_workbook
import pythoncom
import pymysql
from com.atzl.common.Paths import *
from com.atzl.common.StatusCode import StatusCode
from com.atzl.common.ExcelContent import ExcelContent
from com.atzl.functions.accounting.func.Logs import CheckLogs
from com.atzl.functions.accounting.sql.QueryData import QueryDataByBase


class DataCleansing(CheckLogs):

    # 获取所有原始数据表名称
    def get_mydataname(self):
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = 'show tables;'  # 展开表名
        cur.execute(sql)   # 执行sql
        data = cur.fetchall()  # 获取数据
        pd_data = pd.DataFrame(data)  # 将数据存入数据帧
        tables = []  # 定义一个数组容纳表名
        for i in pd_data['Tables_in_mydata']:  # 遍历获取的表名
            tables.append(i)  # 将获取的表名依次压入数组中
        cur.close()
        conn.close()
        return tables


    def select_settlementobj(self, a, table):
        self.Creating_file_Directory()
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        printexts = []
        printextx = []
        try:
            printext = StatusCode.CONNECT_DATABASE
            print(printext)
            self.logs_in(printext)
            ss = list(set(a))
            for i in ss:
                self.Creating_file_Directory()
                printext = StatusCode.BECOUNTING_OBJ + i + "..."
                self.all_message(printext)
                try:
                    has_tabels = self.judge_nw_exist(cur, i)
                    # 不存在西北超标
                    if has_tabels == 1:
                        notnw_diff_store = self.judge_notnw_diff_store(cur, i)
                        # 不分店铺，不存在西北超标
                        if notnw_diff_store == 1:
                            notstore_notnw_price_detail = self.select_price_detail(cur, i, table)
                            if notstore_notnw_price_detail is None:
                                printext = i + StatusCode.NOTHINGNESS_DATA
                                printextx.append(i)
                                self.all_message(printext)
                                continue
                            rows = notstore_notnw_price_detail.shape[0]
                            self.create_bill(i, '', rows, notstore_notnw_price_detail)
                        # 分店铺，不存在西北超标
                        elif notnw_diff_store == 0:
                            store_names = self.query_diffstore_name(cur, i)
                            for name in store_names:
                                diff_store_name_pprice = self.query_isstores_notnw(cur, i, table, name)
                                if diff_store_name_pprice is None:
                                    printext = i + name + StatusCode.NOTHINGNESS_DATA
                                    printextx.append(i)
                                    self.all_message(printext)
                                    continue
                                rows = diff_store_name_pprice.shape[0]
                                self.create_bill(i, name, rows, diff_store_name_pprice)
                    # 存在西北超标
                    elif has_tabels == 0:
                        notnw_diff_store = self.judge_nw_diff_store(cur, i)
                        # 不分店铺,存在西北超标
                        if notnw_diff_store == 1:
                            query_notstore_isnw = self.query_notstore_isnw(cur, i, table)
                            if query_notstore_isnw is None:
                                printext = i + StatusCode.NOTHINGNESS_DATA
                                printextx.append(i)
                                self.all_message(printext)
                                continue
                            rows = query_notstore_isnw.shape[0]
                            self.create_bill(i, '', rows, query_notstore_isnw)
                        # 分店铺,存在西北超标
                        elif notnw_diff_store == 0:
                            store_nameandpolls = self.diff_query_nwsum_quotation(cur, i, table)
                            store_name = store_nameandpolls[0]
                            polls = store_nameandpolls[1]
                            for name, poll in zip(store_name, polls):
                                diff_price_notnw = self.query_isstores_isnw(cur, i, table, poll, name)
                                if diff_price_notnw is None:
                                    printext = i + StatusCode.NOTHINGNESS_DATA
                                    printextx.append(i)
                                    self.all_message(printext)
                                    continue
                                rows = diff_price_notnw.shape[0]
                                self.create_bill(i, name, rows, diff_price_notnw)
                except Exception:
                    printext = i + StatusCode.NOTMAKE_PROJECT
                    printexts.append(i)
                    self.all_message(printext)
                    continue
            if printextx != []:
                print('以下是没数据的工号:')
                for p in printextx:
                    self.all_message(p)
            if printexts != []:
                print('以下是未建工程的工号:')
                for u in printexts:
                    self.all_message(u)
            printexta = StatusCode.CLOSE_CONNECT
            self.all_message(printexta)
            cur.close()
            conn.close()
            return StatusCode.SUBTASK_FINISH
        except Exception:
            printext = StatusCode.NOTHINGNESS_DATA
            self.all_message(printext)
            cur.close()
            conn.close()
            return StatusCode.NOTHINGNESS_DATA

    # 打开文件目录
    def open_files(self):
        try:
            os.startfile(BillPaths.TRANSHIPMENT_CHARGES_DIR)
            return True
        except Exception:
            return False

    # 创建文件目录,如有同名目录不创建,没有同名目录就创建目录
    def Creating_file_Directory(self):
        try:
            path = BillPaths.TRANSHIPMENT_CHARGES_DIR + \
                   BillPaths.QUERY_DIR + \
                   CommonPaths.DIR_TIMESTAMP
            if not os.path.exists(path):
                os.makedirs(path)
            return True
        except Exception:
            return False

    # 查询分店铺,存在西北超票要求
    def query_isstores_isnw(self, cur, obj_id, table, num, name):
        try:
            sql = QueryDataByBase.query_isstores_isnw(obj_id, table, str(int(num)), name)
            cur.execute(sql)
            diff_price_notnw = cur.fetchall()
            if diff_price_notnw == ():
                return None
            diff_prices_notnw = pd.DataFrame(diff_price_notnw)
            return diff_prices_notnw
        except Exception as ex:
            print(ex)



    # 查询分店铺情况下各个店铺的西北超出票数
    def diff_query_nwsum_quotation(self, cur, obj_id, table):
        sql = QueryDataByBase.diff_query_nwsum_quotation(obj_id, table)
        cur.execute(sql)
        store_nameandpoll = cur.fetchall()
        store_nameandpolls = pd.DataFrame(store_nameandpoll)
        store_name = store_nameandpolls['store']
        polls = store_nameandpolls['ex_polls']
        return [store_name, polls]


    # 查询不分店铺,存在西北超票要求
    def query_notstore_isnw(self, cur, obj_id, table):
        num = self.query_nwnum_quotation(cur, obj_id, table)
        sql = QueryDataByBase.query_notstore_isnw(obj_id, table, num)
        cur.execute(sql)
        priec_notnw = cur.fetchall()
        if priec_notnw == ():
            return None
        priecs_notnw = pd.DataFrame(priec_notnw)
        return priecs_notnw
    
    # 查询不分店铺情况下的西北超标票数
    def query_nwnum_quotation(self, cur, obj_id, table):
        sql = QueryDataByBase.query_nwnum_quotation(obj_id, table)
        cur.execute(sql)
        nums_poll = cur.fetchall()
        nums_polls = pd.DataFrame(nums_poll)
        nums_poll = int(nums_polls.iloc[0, 0])
        return nums_poll

    # 查询不考虑西北情况下分店铺的各个店铺名称
    def query_diffstore_name(self, cur, obj_id):
        sql = QueryDataByBase.query_diffstore_name(obj_id)
        cur.execute(sql)
        store_name = cur.fetchall()
        store_names = pd.DataFrame(store_name)
        return store_names['store']


    # 前台提示信息，后台回传用户行为
    def all_message(self, text):
        print(text)
        self.logs_in(text)

    # 生成账单,并吧提示消息传给前台,回传用户行为
    def create_bill(self, obj_id, store_name, rows, data):
        path0 = BillPaths.TRANSHIPMENT_CHARGES_DIR + \
                BillPaths.QUERY_DIR + \
                CommonPaths.DIR_TIMESTAMP + \
                BillPaths.Bill_HEADNAME + \
                obj_id + "$_$" + store_name + \
                BillPaths.CREATE_BILL_TIMESTAMP + FormatPaths.XLSX_FORMAT
        self.set_style_bill(path0, rows, data, obj_id)
        printext = obj_id + store_name + StatusCode.BUILDER_BILL
        self.all_message(printext)

    # 查询分店铺,无西北超票要求
    def query_isstores_notnw(self, cur, obj_id, tables, name):
        sql = QueryDataByBase.query_isstores_notnw(obj_id, tables, name)
        cur.execute(sql)
        diff_store_name = cur.fetchall()
        if diff_store_name == ():
            return None
        diff_store_names = pd.DataFrame(diff_store_name)
        return diff_store_names


    # 判断西北超标报价表是否存在
    def judge_nw_exist(self, cur, obj_id):
        sql = QueryDataByBase.query_havetable_quotation(obj_id)
        cur.execute(sql)
        have_tabels = cur.fetchall()
        has_tabels = pd.DataFrame(have_tabels)
        has_tabels = has_tabels.iloc[0, 0]
        return has_tabels

    # 判断不存在西北超票情况下是否分店铺
    def judge_notnw_diff_store(self, cur, obj_id):
        sql = QueryDataByBase.isornot_notnw_diffstore(obj_id)
        cur.execute(sql)
        diff_store = cur.fetchall()
        diff_store = pd.DataFrame(diff_store)
        diff_store = diff_store.iloc[0, 0]
        return diff_store


    # 判断存在西北超票情况下是否分店铺
    def judge_nw_diff_store(self, cur, obj_id):
        sql = QueryDataByBase.isornot_diffstore(obj_id)
        cur.execute(sql)
        diff_store = cur.fetchall()
        diff_store = pd.DataFrame(diff_store)
        diff_store = diff_store.iloc[0, 0]
        return diff_store



    # 查询报价明细
    def select_price_detail(self, cur, obj_id, table):
        sql = QueryDataByBase.query_notstore_notnw(obj_id, table)
        cur.execute(sql)
        notstore_notnw_price_detail = cur.fetchall()
        if notstore_notnw_price_detail == ():
            return None
        notstore_notnw_price_detail = pd.DataFrame(notstore_notnw_price_detail)
        return notstore_notnw_price_detail


    # 设置账单样式
    def set_style_bill(self, excel_file, rows, data, obj_id):
        pythoncom.CoInitialize()
        nums = data.shape[1]
        if nums == 19:
            newdata = {'扫描时间': data['扫描时间'],
                       '运单号': data['运单号'],
                       '结算重量': data['结算重量'],
                       '中转费': data['中转费'],
                       '西北六省超出占比': data['西北六省发件'],
                       '目的地省': data['目的地省'],
                       '运单发放': data['运单发放']}
        else:
            newdata = {'扫描时间': data['扫描时间'],
                       '运单号': data['运单号'],
                       '结算重量': data['结算重量'],
                       '中转费': data['中转费'],
                       '目的地市': data['目的地市'],
                       '目的地省': data['目的地省'],
                       '运单发放': data['运单发放']}
        pdata = pd.DataFrame(newdata).sort_values(by='扫描时间')
        pdata.insert(4, '疫情加收费', '')
        app = xw.App()
        wb = app.books.add()
        ws = wb.sheets['Sheet1']
        ws.range(f'A2').value = ExcelContent.BILL_TITL
        ws.range(f'A2').api.Font.Size = 18
        ws.range(f'A2').api.Font.Bold = True
        ws.range(f'A2:H2').api.Merge()
        ws.range(f'A2:H2').api.HorizontalAlignment = -4108  # -4108 水平居中。 -4131 靠左，-4152 靠右。
        ws.range(f'A2:H2').api.VerticalAlignment = -4108  # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
        ws.range(f'A3').value = obj_id + ExcelContent.SAY_HELLO
        ws.range(f'A3').api.Font.Size = 18
        ws.range(f'A3').api.Font.Bold = True
        ws.range(f'A3:H3').api.Merge()
        ws.range(f'A3:H3').api.HorizontalAlignment = -4131  # -4108 水平居中。 -4131 靠左，-4152 靠右。
        ws.range(f'A3:H3').api.VerticalAlignment = -4108   # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
        ws.range(f'A4').value = ExcelContent.EXPLAIN_CONTENT0
        ws.range(f'A4').api.Font.Size = 18
        ws.range(f'A4').api.Font.Bold = True
        ws.range(f'A4:H4').api.Merge()
        ws.range(f'A4:H4').api.HorizontalAlignment = -4131  # -4108 水平居中。 -4131 靠左，-4152 靠右。
        ws.range(f'A4:H4').api.VerticalAlignment = -4108  # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
        ws.range(f'A5').value = ExcelContent.EXPLAIN_CONTENT1
        ws.range(f'A5').api.Font.Size = 18
        ws.range(f'A5').api.Font.Bold = True
        ws.range(f'A5:H5').api.Merge()
        ws.range(f'A5:H5').api.HorizontalAlignment = -4131  # -4108 水平居中。 -4131 靠左，-4152 靠右。
        ws.range(f'A5:H5').api.VerticalAlignment = -4108  # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
        ws.range(f'A6:H6').color = 51, 102, 255
        ws.range(f'A6:H6').api.Font.ColorIndex = 2
        ws.range(f'A6').value = '扫描时间'
        ws.range(f'B6').value = '运单号'
        ws.range(f'C6').value = '结算重量'
        ws.range(f'D6').value = '中转费'
        ws.range(f'E6').value = '疫情费用'
        if nums == 19:
            ws.range(f'F6').value = '西北六省超出占比'
        else:
            ws.range(f'F6').value = '目的地市'
        ws.range(f'G6').value = '目的地省'
        ws.range(f'H6').value = '运单发放'
        ws.range(f'A1:H1').column_width = 16
        ws.range(f'A1:H1').row_height = 150
        ws.range(f'A2:H{rows + 7}').column_width = 22
        ws.range(f'A2:H{rows + 7}').row_height = 25
        wb.save(excel_file)
        wb.close()
        app.quit()
        book = load_workbook(excel_file)
        write = pd.ExcelWriter(excel_file, engine='openpyxl')
        write.book = book
        write.sheets = {ws.title: ws for ws in book.worksheets}
        pdata.to_excel(write, sheet_name='Sheet1', header=False, index=False, startrow=6, startcol=0)
        write.close()
        app1 = xw.App(visible=False, add_book=False)
        wb1 = app1.books.open(excel_file)
        ws1 = wb1.sheets['Sheet1']
        wb1.app.range("A7").select()
        wb1.app.api.ActiveWindow.FreezePanes = True
        a_range = f'A6:H{rows + 15}'
        ws1.range(a_range).api.Borders(8).LineStyle = 1  # 上边框
        ws1.range(a_range).api.Borders(9).LineStyle = 1  # 下边框
        ws1.range(a_range).api.Borders(7).LineStyle = 1  # 左边框
        ws1.range(a_range).api.Borders(10).LineStyle = 1  # 右边框
        ws1.range(a_range).api.Borders(12).LineStyle = 1  # 内横边框
        ws1.range(a_range).api.Borders(11).LineStyle = 1  # 内纵边框
        # 设置合计样式
        ws1.range(f'A{rows + 7}').value = '合计'
        ws1.range(f'A{rows + 7}').api.Font.Size = 18
        ws1.range(f'A{rows + 7}').api.Font.Bold = True
        ws1.range(f'A{rows + 7}').api.HorizontalAlignment = -4108
        ws1.range(f'A{rows + 7}').api.VerticalAlignment = -4108
        ws1.range(f'B{rows + 7}').value = str(rows) + "票"
        ws1.range(f'B{rows + 7}').api.Font.Size = 18
        ws1.range(f'B{rows + 7}').api.Font.Bold = True
        ws1.range(f'B{rows + 7}').api.HorizontalAlignment = -4108
        ws1.range(f'B{rows + 7}').api.VerticalAlignment = -4108
        ws1.range(f'D{rows + 7}').formula = f'=sum(D7:D{rows+6})'
        ws1.range(f'D{rows + 7}').api.Font.Size = 18
        ws1.range(f'D{rows + 7}').api.Font.Bold = True
        ws1.range(f'D{rows + 7}').api.HorizontalAlignment = -4108
        ws1.range(f'D{rows + 7}').api.VerticalAlignment = -4108
        if nums == 19:
            ws1.range(f'F{rows + 7}').formula = f'=sum(F7:F{rows + 6})'
            ws1.range(f'F{rows + 7}').api.Font.Size = 18
            ws1.range(f'F{rows + 7}').api.Font.Bold = True
            ws1.range(f'F{rows + 7}').api.HorizontalAlignment = -4108
            ws1.range(f'F{rows + 7}').api.VerticalAlignment = -4108
        ws1.range(f'E{rows + 7}').formula = f'=sum(E7:E{rows + 6})'
        ws1.range(f'E{rows + 7}').api.Font.Size = 18
        ws1.range(f'E{rows + 7}').api.Font.Bold = True
        ws1.range(f'E{rows + 7}').api.HorizontalAlignment = -4108
        ws1.range(f'E{rows + 7}').api.VerticalAlignment = -4108
        ws1.range(f'G{rows + 7}').value = '总计'
        ws1.range(f'G{rows + 7}').color = 255, 250, 0
        ws1.range(f'G{rows + 7}').api.Font.Size = 18
        ws1.range(f'G{rows + 7}').api.Font.Bold = True
        ws1.range(f'G{rows + 7}').api.HorizontalAlignment = -4108
        ws1.range(f'G{rows + 7}').api.VerticalAlignment = -4108
        ws1.range(f'H{rows + 7}').formula = f'=D{rows + 7} + E{rows + 7} + F{rows + 7}'
        ws1.range(f'H{rows + 7}').api.Font.Size = 18
        ws1.range(f'H{rows + 7}').api.Font.Bold = True
        ws1.range(f'H{rows + 7}').api.HorizontalAlignment = -4108
        ws1.range(f'H{rows + 7}').api.VerticalAlignment = -4108
        total_amount_range = f'A{rows + 8}:B{rows + 8}'
        identify_the_amount = f'C{rows + 8}:D{rows + 8}'
        remark_range = f'A{rows + 9}:H{rows + 9}'
        remark_range1 = f'A{rows + 10}:H{rows + 10}'
        company_name_range = f'A{rows + 11}:D{rows + 11}'
        company_name_range1 = f'E{rows + 11}:H{rows + 11}'
        alipay_range = f'A{rows + 12}:H{rows + 12}'
        alipay_range1 = f'A{rows + 13}:H{rows + 13}'
        alipay_range2 = f'A{rows + 14}:C{rows + 14}'
        alipay_range3 = f'D{rows + 14}:H{rows + 14}'
        alipay_range4 = f'A{rows + 15}:H{rows + 15}'
        # 合并单元格
        ws1.range(total_amount_range).api.Merge()
        ws1.range(f'A{rows + 8}').value = '总计应付金额:'
        ws1.range(f'A{rows + 8}').api.Font.Size = 18
        ws1.range(f'A{rows + 8}').api.Font.Bold = True
        ws1.range(f'A{rows + 8}').api.HorizontalAlignment = -4108
        ws1.range(f'A{rows + 8}').api.VerticalAlignment = -4108
        ws1.range(identify_the_amount).api.Merge()
        ws1.range(f'C{rows + 8}').formula = f'="￥"&TEXT(H{rows + 7},"0.00")'
        ws1.range(f'C{rows + 8}').api.Font.Size = 18
        ws1.range(f'C{rows + 8}').api.Font.Bold = True
        ws1.range(f'C{rows + 8}').api.HorizontalAlignment = -4108
        ws1.range(f'C{rows + 8}').api.VerticalAlignment = -4108
        ws1.range(remark_range).api.Merge()
        ws1.range(f'A{rows + 9}').value = ExcelContent.EXPLAIN_CONTENT2
        ws1.range(f'A{rows + 9}').api.Font.Size = 15
        ws1.range(f'A{rows + 9}').api.Font.Bold = True
        ws1.range(f'A{rows + 9}').api.HorizontalAlignment = -4108
        ws1.range(f'A{rows + 9}').api.VerticalAlignment = -4108
        ws1.range(remark_range1).api.Merge()
        ws1.range(f'A{rows + 10}').value = ExcelContent.EXPLAIN_CONTENT3
        ws1.range(f'A{rows + 10}').api.Font.Size = 15
        ws1.range(f'A{rows + 10}').api.Font.Bold = True
        ws1.range(f'A{rows + 10}').api.HorizontalAlignment = -4108
        ws1.range(f'A{rows + 10}').api.VerticalAlignment = -4108
        ws1.range(company_name_range).api.Merge()
        ws1.range(f'A{rows + 11}').value = ExcelContent.EXPLAIN_CONTENT4
        ws1.range(f'A{rows + 11}').api.Font.Size = 15
        ws1.range(f'A{rows + 11}').api.Font.Bold = True
        ws1.range(f'A{rows + 11}').api.HorizontalAlignment = -4108
        ws1.range(f'A{rows + 11}').api.VerticalAlignment = -4108
        ws1.range(company_name_range1).api.Merge()
        ws1.range(f'E{rows + 11}').value = ExcelContent.EXPLAIN_CONTENT5
        ws1.range(f'E{rows + 11}').api.Font.Size = 15
        ws1.range(f'E{rows + 11}').api.Font.Bold = True
        ws1.range(f'E{rows + 11}').api.HorizontalAlignment = -4108
        ws1.range(f'E{rows + 11}').api.VerticalAlignment = -4108
        ws1.range(alipay_range).api.Merge()
        ws1.range(f'A{rows + 12}').value = ExcelContent.EXPLAIN_CONTENT6
        ws1.range(f'A{rows + 12}').api.Font.Size = 15
        ws1.range(f'A{rows + 12}').api.Font.Bold = True
        ws1.range(f'A{rows + 12}').api.HorizontalAlignment = -4108
        ws1.range(f'A{rows + 12}').api.VerticalAlignment = -4108
        ws1.range(alipay_range1).api.Merge()
        ws1.range(f'A{rows + 13}').value = ExcelContent.EXPLAIN_CONTENT7
        ws1.range(f'A{rows + 13}').api.Font.Size = 15
        ws1.range(f'A{rows + 13}').api.Font.Bold = True
        ws1.range(f'A{rows + 13}').api.HorizontalAlignment = -4108
        ws1.range(f'A{rows + 13}').api.VerticalAlignment = -4108
        ws1.range(alipay_range2).api.Merge()
        ws1.range(f'A{rows + 14}').value = ExcelContent.EXPLAIN_CONTENT8
        ws1.range(f'A{rows + 14}').api.Font.Size = 15
        ws1.range(f'A{rows + 14}').api.Font.Bold = True
        ws1.range(f'A{rows + 14}').api.HorizontalAlignment = -4108
        ws1.range(f'A{rows + 14}').api.VerticalAlignment = -4108
        ws1.range(alipay_range3).api.Merge()
        ws1.range(f'D{rows + 14}').value = ExcelContent.EXPLAIN_CONTENT9
        ws1.range(f'D{rows + 14}').api.Font.Size = 15
        ws1.range(f'D{rows + 14}').api.Font.Bold = True
        ws1.range(f'D{rows + 14}').api.HorizontalAlignment = -4108
        ws1.range(f'D{rows + 14}').api.VerticalAlignment = -4108
        ws1.range(alipay_range4).api.Merge()
        ws1.range(f'A{rows + 15}').value = ExcelContent.EXPLAIN_CONTENT10
        ws1.range(f'A{rows + 15}').api.Font.Size = 15
        ws1.range(f'A{rows + 15}').api.Font.Bold = True
        ws1.range(f'A{rows + 15}').api.HorizontalAlignment = -4108
        ws1.range(f'A{rows + 15}').api.VerticalAlignment = -4108
        ws1.range(f'A{rows + 8}:H{rows + 15}').row_height = 60
        fileName = os.path.join(os.getcwd(), ImagesPaths.BILL_TOP_LOG)
        ws1.pictures.add(fileName,
                         left=ws1.range('A1').left,
                         top=ws1.range('A1').top,
                         width=1200,
                         height=150)
        wb1.save(excel_file)
        wb1.close()
        app1.quit()
